﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using HRProject.Code.Common;

namespace HRProject.Code.DAO
{
    public class MySqlDataSource
    {
        private MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["VatBillDb"].ConnectionString);

        public bool Ping()
        {
            bool result = true;
            try
            {
                connection.Open();
                connection.Close();
            }
            catch (MySqlException exp)
            {
                result = false;
                Console.Out.WriteLine(exp.Message);
            }
            return result;
        }

        public int Execute(string command, List<MySqlParameter> parameters, bool useTransaction)
        {
            int result = 0;
            MySqlTransaction transaction = null;
            try
            {
                if (!connection.State.Equals(ConnectionState.Open))
                {
                    connection.Open();
                }

                MySqlCommand sqlCommand = connection.CreateCommand();
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = command;
                if (useTransaction)
                {
                    transaction = connection.BeginTransaction();
                    sqlCommand.Transaction = transaction;
                }

                for (int i = 0; i < parameters.Count; i++)
                    sqlCommand.Parameters.Add(parameters[i]);
                result = sqlCommand.ExecuteNonQuery();

                if (transaction != null)
                    transaction.Commit();

                connection.Close();
            }
            catch (Exception exp)
            {
                if (transaction != null)
                    transaction.Rollback();
                if (connection.State.Equals(ConnectionState.Open))
                    connection.Close();
                Console.Out.WriteLine(exp.Message);
            }
            return result;
        }

        public int Execute(List<MySqlCommand> commands, bool useTransaction)
        {
            int result = 0;
            MySqlTransaction transaction = null;
            try
            {
                connection.Open();

                if (useTransaction)
                    transaction = connection.BeginTransaction();

                foreach (MySqlCommand c in commands)
                {
                    if (c.Connection == null)
                        c.Connection = connection;
                    c.Transaction = transaction;
                    result = c.ExecuteNonQuery();
                }

                if (transaction != null)
                    transaction.Commit();
                result = 1;
                connection.Close();
            }
            catch (MySqlException exp)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                    result = 0;
                }
                if (connection.State.Equals(ConnectionState.Open))
                    connection.Close();
                Console.Write(exp.StackTrace);
                //System.Windows.Forms.MessageBox.Show(exp.Message);
            }
            return result;
        }

        public DataTable ExecuteQuery(string command, List<MySqlParameter> parameters, bool useTransaction)
        {
            MySqlTransaction transaction = null;
            DataTable dataTable = new DataTable();
            try
            {
                if (!connection.State.Equals(ConnectionState.Open))
                {
                    connection.Open();
                }

                if (useTransaction)
                    transaction = connection.BeginTransaction();

                MySqlCommand sqlCommand = connection.CreateCommand();
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = command;
                for (int i = 0; i < parameters.Count; i++)
                    sqlCommand.Parameters.Add(parameters[i]);
                MySqlDataReader reader = sqlCommand.ExecuteReader();
                for (int i = 0; i < reader.FieldCount; i++)
                    dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                while (reader.Read())
                {
                    DataRow row = dataTable.NewRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (DBNull.Value.Equals(reader[i]))
                        {
                            Type type = reader.GetFieldType(i);
                            if (typeof(string).Equals(type))
                                row[i] = string.Empty;
                            else if (typeof(bool).Equals(type))
                                row[i] = false;
                            else if (typeof(byte).Equals(type))
                                row[i] = 0;
                            else if (typeof(short).Equals(type))
                                row[i] = 0;
                            else if (typeof(int).Equals(type))
                                row[i] = 0;
                            else if (typeof(uint).Equals(type))
                                row[i] = 0;
                            else if (typeof(float).Equals(type))
                                row[i] = 0;
                            else if (typeof(long).Equals(type))
                                row[i] = 0;
                            else if (typeof(ulong).Equals(type))
                                row[i] = 0;
                            else if (typeof(decimal).Equals(type))
                                row[i] = 0;
                            else if (typeof(double).Equals(type))
                                row[i] = 0;
                            else if (typeof(DateTime).Equals(type))
                                row[i] = Constant.DefaultMinDate;
                            else if (typeof(byte[]).Equals(type))
                                row[i] = new byte[0];
                            else if (typeof(Guid).Equals(type))
                                row[i] = Guid.Empty;
                        }
                        else
                            row[i] = reader[i];
                    }
                    dataTable.Rows.Add(row);
                }
                reader.Close();

                if (transaction != null)
                    transaction.Commit();

                connection.Close();
            }
            catch (MySqlException exp)
            {
                if (transaction != null)
                    transaction.Rollback();
                if (connection.State.Equals(ConnectionState.Open))
                    connection.Close();
                Console.Out.WriteLine(exp.Message);
            }
            return dataTable;
        }
    }
}
